﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.OleDb;

/// <summary>
/// Summary description for DB
/// </summary>
public class DB
{
    OleDbCommand cmd;
    OleDbConnection baglanti;
	public DB()
	{
        baglanti = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=" + HttpContext.Current.Server.MapPath("~/App_Data/acs.mdb"));      
	}
    public int ToplamMakaleSayisi()
    {
        cmd = new OleDbCommand("select count(*) from Icerik", baglanti);
        BaglantiKontrol();
        int adet = Convert.ToInt32(cmd.ExecuteScalar());
        Kapat();
        return adet;
    }
    public int ToplamZiyaretci()
    {
        cmd = new OleDbCommand("select count(*) from (select distinct ip from Stat) as a", baglanti);
        BaglantiKontrol();
        int adet = Convert.ToInt32(cmd.ExecuteScalar());
        Kapat();
        return adet;
    }
    public int SayfaGoruntuleme()
    {
        cmd = new OleDbCommand("select count(*) from Stat", baglanti);
        BaglantiKontrol();
        int adet = Convert.ToInt32(cmd.ExecuteScalar());
        Kapat();
        return adet;
    }
    public int ToplamYorum()
    {
        cmd = new OleDbCommand("select count(*) from Yorumlar", baglanti);
        BaglantiKontrol();
        int adet = Convert.ToInt32(cmd.ExecuteScalar());
        Kapat();
        return adet;
    }
    
    public void YorumEkle(string icerikid, string kimden,string yorum)
    {
        cmd = new OleDbCommand("insert into Yorumlar(kimden,yorum,icerikid,onay,tarih) values(?,?,?,?,?)", baglanti);
        cmd.Parameters.Add("a", OleDbType.VarChar).Value = kimden;
        cmd.Parameters.Add("b", OleDbType.VarChar).Value = yorum;
        cmd.Parameters.Add("c", OleDbType.Integer).Value = icerikid;
        cmd.Parameters.Add("d", OleDbType.Boolean).Value = false;
        cmd.Parameters.Add("e", OleDbType.Date).Value = DateTime.Now;
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        Kapat();
    }
    public void YorumSil(string yorumid)
    {
        cmd = new OleDbCommand("delete from Yorumlar where id=?", baglanti);
        cmd.Parameters.Add("a", OleDbType.Integer).Value = yorumid;
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        Kapat();
    }
    public void YorumOnayla(string yorumid)
    {
        cmd = new OleDbCommand("update Yorumlar set onay=true where id=?", baglanti);
        cmd.Parameters.Add("a", OleDbType.Integer).Value = yorumid;
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        Kapat();

    }
    public DataRow MenuBilgisiGetir(string Menu)
    {
        cmd = new OleDbCommand("select * from Menuler where menu=?", baglanti);
        cmd.Parameters.Add("a", OleDbType.VarChar).Value = Menu;
        BaglantiKontrol();
        DataTable tablo = new DataTable();
        OleDbDataReader oku = cmd.ExecuteReader();
        tablo.Load(oku);
        oku.Close();
        baglanti.Close();
        return tablo.Rows[0];
    }
    public string KategoriAdGetir(string id)
    {
        

        cmd = new OleDbCommand("select ad from Kategoriler where id=?", baglanti);
        cmd.Parameters.Add("a", OleDbType.Integer).Value = id;
        BaglantiKontrol();
        string ad = cmd.ExecuteScalar().ToString();
        baglanti.Close();
        return ad;
    }
    public int IcerikAdetGetir(string katid)
    {
        cmd = new OleDbCommand("select count(*) from Icerik where katid=?", baglanti);
        cmd.Parameters.Add("a", OleDbType.Integer).Value = katid;
        BaglantiKontrol();
        int adet = Convert.ToInt32(cmd.ExecuteScalar());
        Kapat();
        return adet;
    }
    public DataRow IcerikGetir(string id)
    {
        cmd = new OleDbCommand("select * from Icerik where id=?", baglanti);
        cmd.Parameters.Add("a", OleDbType.Integer).Value = id;
        BaglantiKontrol();
        DataTable tablo = new DataTable();
        OleDbDataReader oku = cmd.ExecuteReader();
        tablo.Load(oku);
        oku.Close();
        baglanti.Close();
        return tablo.Rows[0];
    }
    public static string MesajGetir(string kod,bool forJs)
    {
        string msj= HttpContext.GetGlobalResourceObject("Mesajlar", kod).ToString();
        string script = "return window.confirm('" + msj + "');";
        return forJs ? script : msj;
    }
    public DataRow MenuIcerikGetir(string menuid)
    {
        cmd = new OleDbCommand("select * from Menuler where id=?", baglanti);
        cmd.Parameters.Add("a", OleDbType.Integer).Value = menuid;
        BaglantiKontrol();
        DataTable tablo = new DataTable();
        OleDbDataReader oku = cmd.ExecuteReader();
        tablo.Load(oku);
        oku.Close();
        baglanti.Close();
        return tablo.Rows[0];
    }
    public void MenuGuncelle(string menuid, string icerik)
    {
        cmd = new OleDbCommand("update Menuler set icerik=?,durum=1 where id=?", baglanti);
        cmd.Parameters.Add("a", OleDbType.VarChar).Value = icerik;
        cmd.Parameters.Add("b", OleDbType.Integer).Value = menuid;
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        baglanti.Close();
    }
    public void MenuGaleriGuncelle(string menuid, string galeriid)
    {
        cmd = new OleDbCommand("update Menuler set galerid=?,durum=0 where id=?", baglanti);
        cmd.Parameters.Add("a", OleDbType.VarChar).Value = galeriid;
        cmd.Parameters.Add("b", OleDbType.Integer).Value = menuid;
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        baglanti.Close();
    }

    public void GaleriSil(string id)
    {
        cmd = new OleDbCommand("GaleriSil", baglanti);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("id", OleDbType.Integer).Value = id;      
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        Kapat();
    }
    public void GaleriResimSil(string id)
    {
        cmd = new OleDbCommand("GaleriResimSil", baglanti);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("id", OleDbType.Integer).Value = id;
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        Kapat();
    }
    public bool GirisKontrol(string kadi, string sifre)
    {
        cmd = new OleDbCommand("KullaniciKontrol", baglanti);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("kadi", OleDbType.VarChar).Value = kadi;
        cmd.Parameters.Add("sifre", OleDbType.VarChar).Value = sifre;
        BaglantiKontrol();
        OleDbDataReader oku = cmd.ExecuteReader();
        if (oku.Read())
        {
            oku.Close();
            baglanti.Close();
            return true;
        }
        oku.Close();
        baglanti.Close();
        return false;

    }
    public bool SifreDegistir(string eski, string yeni)
    {
        if (GirisKontrol("snnucr", eski))
        {
            cmd = new OleDbCommand("SifDegistir", baglanti);
            cmd.CommandType = CommandType.StoredProcedure;    
            cmd.Parameters.Add("sif", OleDbType.VarChar).Value = yeni;
            BaglantiKontrol();
            cmd.ExecuteNonQuery();
            baglanti.Close();
            return true;
        }
        return false;
    }
    private void BaglantiKontrol()
    {
        if (baglanti.State == ConnectionState.Closed)
            baglanti.Open();
    }
    public void ResimEkle(string id, string resim)
    {
        cmd = new OleDbCommand("ResimEkle", baglanti);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("id", OleDbType.Integer).Value = id;
        cmd.Parameters.Add("resim", OleDbType.VarChar).Value = resim;
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        Kapat();
    }
    public bool ResimKontrol(string id, string resim)
    {
        cmd = new OleDbCommand("ResimKontrol", baglanti);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("id", OleDbType.Integer).Value = id;
        cmd.Parameters.Add("resim", OleDbType.VarChar).Value = resim;
        BaglantiKontrol();
        OleDbDataReader oku = cmd.ExecuteReader();
        if (oku.Read())
        {
            oku.Close();
            baglanti.Close();
            return false;
        }
        oku.Close();
        baglanti.Close();
        return true;
    }
    public void GaleriEkle(string ad)
    {
        cmd = new OleDbCommand("GaleriEkle", baglanti);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("ad", OleDbType.VarChar).Value = ad;
        cmd.Parameters.Add("ad2", OleDbType.VarChar).Value = TurkceYap(ad);
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        Kapat();

    }
    private void Kapat()
    {
        baglanti.Close();
    }
    public string GaleriAdGetir(string id)
    {
        cmd = new OleDbCommand("select galeri from Galeriler where id=?", baglanti);
        cmd.Parameters.Add("a", OleDbType.Integer).Value = id;
        BaglantiKontrol();
        try
        {
            string ad = cmd.ExecuteScalar().ToString();
            baglanti.Close();
            return ad;
        }
        catch
        {
            Kapat();
        return "";
        }
    }
    public void IcerikSil(string katid)
    {
        cmd = new OleDbCommand("IcerikSil", baglanti);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("katid", OleDbType.Integer).Value = katid;
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        Kapat();
    }
    public void DuyuruEkle(string baslik, bool ana, string icerik)
    {
        cmd = new OleDbCommand("insert into Duyurular(baslik,ana,duyuru) values(?,?,?)", baglanti);
        cmd.Parameters.Add("a", OleDbType.VarChar).Value = baslik;
        cmd.Parameters.Add("b", OleDbType.Boolean).Value = ana;
        cmd.Parameters.Add("c", OleDbType.VarChar).Value = icerik;
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        Kapat();
    }
    public string DuyuruGetir(string id)
    {
        cmd = new OleDbCommand("select duyuru from Duyurular where id=?", baglanti);
        cmd.Parameters.Add("i", OleDbType.Integer).Value = id;
        BaglantiKontrol();
        try
        {
            string duy = cmd.ExecuteScalar().ToString();
            Kapat();
            return duy;
        }
        catch
        {
            Kapat();
            return "";
        }         

    }
    public string TurkceYap(string deger)
    {
        deger = deger.ToLower();
        deger = deger.Replace("#", "Sharp");
        deger = deger.Replace(' ', '-');
        deger = deger.Replace('ü', 'u');
        deger = deger.Replace('ı', 'i');
        deger = deger.Replace('ö', 'o');
        deger = deger.Replace('ğ', 'g');
        deger = deger.Replace('ç', 'c');
        deger = deger.Replace("'", "-");
        deger = deger.Replace('ş', 's');
        return deger;
    }
    public void IcerikEkle(string baslik, int katid, int sira,string spot, string icerik)
    {
        cmd = new OleDbCommand("IcerikEkle", baglanti);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@baslik", OleDbType.VarChar).Value = baslik;
        cmd.Parameters.Add("@baslik2", OleDbType.VarChar).Value = TurkceYap(baslik);
        cmd.Parameters.Add("@katid", OleDbType.Integer).Value = katid;
        cmd.Parameters.Add("@sira", OleDbType.Integer).Value = sira;
        cmd.Parameters.Add("@spot", OleDbType.VarChar).Value = spot;
        cmd.Parameters.Add("@icerik", OleDbType.VarChar).Value = icerik;
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        Kapat();
    }
    public void StatGir(string ip, string browser, string bil, string sayfa)
    {
        cmd = new OleDbCommand("insert into Stat(ip,browser,biladi,sayfa,tarih) values(?,?,?,?,?)", baglanti);
        cmd.Parameters.Add("a", OleDbType.VarChar).Value = ip;
        cmd.Parameters.Add("b", OleDbType.VarChar).Value = browser;
        cmd.Parameters.Add("c", OleDbType.VarChar).Value = bil;
        cmd.Parameters.Add("d", OleDbType.VarChar).Value = sayfa;
        cmd.Parameters.Add("e", OleDbType.Date).Value = DateTime.Now;
        BaglantiKontrol();
        cmd.ExecuteNonQuery();
        Kapat();
    }
}
